Application
This unit describes the skills and knowledge required to retrieve and manipulate information stored in information systems using a query language.
It applies to those who are involved in a range of work environments, who are required to extract information from a relational database by creating and running queries.
No licensing, legislative or certification requirements apply to this unit at the time of publication.
Elements and Performance Criteria
1. Determine requirements of developing queries | 1.1 Identify terminologies applicable to query language 1.2 Identify and build queries using required tools and environment |
2. Write queries to extract required information | 2.1 Sort and selectively retrieve values through query writing 2.2 Aggregate records by multiple attributes using an expression 2.3 Sort and filter aggregated records using an expression 2.4 Create queries and retrieve records from multiple tables 2.5 Retrieve records from multiple tables using different expressions and approaches 2.6 Filter records from multiple tables using an expression 2.7 Retrieve records from multiple tables using expressions, set operators and set logic 2.8 Construct and execute sub-queries |
3. Perform calculations and use expressions in queries | 3.1 Perform a calculation on numeric values and an operation on text values using an expression 3.2 Perform an operation on date and time values using functions 3.3 Aggregate values and obtain required output using an expression |
4. Create and manipulate tables | 4.1 Identify required columns, data types, keys, relationships, indexes and constraints 4.2 Use required naming conventions for database elements 4.3 Create tables that implement required elements and use check constraints 4.4 Manipulate tables according to specific requirements |
5. Create and use views and stored procedures | 5.1 Create views according to information requirements 5.2 Retrieve and save data using a view and transactions 5.3 Drop a view from a database 5.4 Retrieve, insert and modify data using created and stored procedures according to information requirements 5.5 Create and execute stored procedures using one or more parameters 5.6 Drop a stored procedure from database 5.7 Create and test database triggers and perform specific required data-related functions |
Evidence of Performance
The candidate must demonstrate the ability to complete the tasks outlined in the elements, performance criteria and foundation skills of this unit, including evidence of the ability to:
develop, store and execute at least three queries, and retrieve and extract data at least three times
perform at least three calculations using control statements, operators and functions
prepare and manipulate at least three tables to meet specific requirements
create and use at least two views and at least two stored procedures according to task requirements.
Evidence of Knowledge
The candidate must be able to demonstrate knowledge to complete the tasks outlined in the elements, performance criteria and foundation skills of this unit, including knowledge of:
information gathering methods that may be used to apply query language in relational databases
industry standard database management systems (DBMS)
methods of connecting to DBMS
using data definition language (DDL), data manipulation language (DML) and data control language (DCL)
data analysis techniques
data mining
data source characteristics
information system features
required systems and procedures that may be used to apply query language in relational databases.
Assessment Conditions
Skills in this unit must be demonstrated in a workplace or simulated environment where the conditions are typical of those in a working environment in this industry.
This includes access to:
industry standard software packages
required hardware and its components
a data source
resources in chosen query language
documentation regarding client and functionality requirements
resources to aid calculation.
Assessors of this unit must satisfy the requirements for assessors in applicable vocational education and training legislation, frameworks and/or standards.
Foundation Skills
Numeracy | Makes basic calculations involving arithmetical operations Makes estimates and comparisons involving date and time data |
Reading | Determines requirements by analysing and interpreting technical data |
Writing | Writes and edits code and technical data in a logical manner using required syntax |
Technology | Stores and manages data using digitally based technologies and systems |
Sectors
Programming and software development